package com.insight.export.generator;

import com.insight.common.enums.ExportFormat;
import com.insight.entity.DataSource;
import com.insight.entity.MetadataColumn;
import com.insight.entity.MetadataTable;
import com.insight.repository.MetadataColumnRepository;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;

/**
 * T089: Excel格式数据字典生成器
 * <p>
 * 使用Apache POI生成.xlsx格式的数据字典文档.
 * 包含多个工作表: 数据源信息、表目录、各表详情等.
 * </p>
 *
 * <p><b>工作簿结构</b>:</p>
 * <ul>
 *   <li>工作表1: 数据源信息 - 数据源基本信息</li>
 *   <li>工作表2: 表目录 - 所有表的汇总列表</li>
 *   <li>工作表3+: 各表详情 - 每个表一个工作表(包含字段信息)</li>
 * </ul>
 *
 * @author Insight Team
 */
@Slf4j
@Component("EXCEL")
@RequiredArgsConstructor
public class ExcelExportGenerator implements ExportGenerator {

    private final MetadataColumnRepository columnRepository;

    private static final DateTimeFormatter TIMESTAMP_FORMAT =
            DateTimeFormatter.ofPattern("yyyyMMdd_HHmmss");

    // 样式常量
    private CellStyle headerStyle;
    private CellStyle dataStyle;
    private CellStyle titleStyle;

    @Override
    public Path generate(DataSource dataSource, List<MetadataTable> tables, Path outputDir)
            throws IOException {
        // 参数验证
        validateParameters(dataSource, tables, outputDir);

        // 确保输出目录存在
        if (!Files.exists(outputDir)) {
            Files.createDirectories(outputDir);
        }

        // 生成文件名: datasource_timestamp.xlsx
        String timestamp = LocalDateTime.now().format(TIMESTAMP_FORMAT);
        String fileName = String.format("%s_%s.xlsx", sanitizeFileName(dataSource.getName()), timestamp);
        Path outputFile = outputDir.resolve(fileName);

        // 创建工作簿
        try (Workbook workbook = new XSSFWorkbook()) {
            // 初始化样式
            initializeStyles(workbook);

            // 创建工作表
            createDataSourceInfoSheet(workbook, dataSource);
            createTableOfContentsSheet(workbook, tables);

            // 创建各表详情工作表(包含字段信息)
            createTableDetailSheets(workbook, tables);

            // 写入文件
            try (FileOutputStream fos = new FileOutputStream(outputFile.toFile())) {
                workbook.write(fos);
            }
        }

        return outputFile;
    }

    @Override
    public ExportFormat getSupportedFormat() {
        return ExportFormat.EXCEL;
    }

    @Override
    public String getFileExtension() {
        return "xlsx";
    }

    /**
     * 验证参数
     */
    private void validateParameters(DataSource dataSource, List<MetadataTable> tables, Path outputDir) {
        if (dataSource == null) {
            throw new IllegalArgumentException("数据源不能为null");
        }
        if (tables == null) {
            throw new IllegalArgumentException("表列表不能为null");
        }
        if (outputDir == null) {
            throw new IllegalArgumentException("输出目录不能为null");
        }
    }

    /**
     * 初始化单元格样式
     */
    private void initializeStyles(Workbook workbook) {
        // 标题样式
        titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 14);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        // 表头样式
        headerStyle = workbook.createCellStyle();
        Font headerFont = workbook.createFont();
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        headerStyle.setFont(headerFont);
        headerStyle.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex());
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        // 数据样式
        dataStyle = workbook.createCellStyle();
        dataStyle.setBorderBottom(BorderStyle.THIN);
        dataStyle.setBorderTop(BorderStyle.THIN);
        dataStyle.setBorderLeft(BorderStyle.THIN);
        dataStyle.setBorderRight(BorderStyle.THIN);
    }

    /**
     * 创建数据源信息工作表
     */
    private void createDataSourceInfoSheet(Workbook workbook, DataSource dataSource) {
        Sheet sheet = workbook.createSheet("数据源信息");

        int rowNum = 0;

        // 标题行
        Row titleRow = sheet.createRow(rowNum++);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("数据源信息");
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, 1));

        rowNum++; // 空行

        // 表头
        Row headerRow = sheet.createRow(rowNum++);
        createHeaderCell(headerRow, 0, "项目");
        createHeaderCell(headerRow, 1, "值");

        // 数据行
        createDataRow(sheet, rowNum++, "数据源名称", dataSource.getName());
        createDataRow(sheet, rowNum++, "数据库类型", dataSource.getDbType().toString());
        createDataRow(sheet, rowNum++, "主机地址", dataSource.getHost() + ":" + dataSource.getPort());
        createDataRow(sheet, rowNum++, "数据库名",
                dataSource.getDatabaseName() != null ? dataSource.getDatabaseName() : "N/A");
        createDataRow(sheet, rowNum++, "用户名", dataSource.getUsername());

        // 自动调整列宽
        sheet.autoSizeColumn(0);
        sheet.autoSizeColumn(1);
        sheet.setColumnWidth(0, sheet.getColumnWidth(0) + 1000);
        sheet.setColumnWidth(1, sheet.getColumnWidth(1) + 1000);
    }

    /**
     * 创建表目录工作表
     */
    private void createTableOfContentsSheet(Workbook workbook, List<MetadataTable> tables) {
        Sheet sheet = workbook.createSheet("表目录");

        int rowNum = 0;

        // 标题行
        Row titleRow = sheet.createRow(rowNum++);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("表目录");
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, 3));

        rowNum++; // 空行

        if (tables.isEmpty()) {
            Row emptyRow = sheet.createRow(rowNum);
            Cell emptyCell = emptyRow.createCell(0);
            emptyCell.setCellValue("暂无表信息");
            return;
        }

        // 表头
        Row headerRow = sheet.createRow(rowNum++);
        createHeaderCell(headerRow, 0, "表名");
        createHeaderCell(headerRow, 1, "注释");
        createHeaderCell(headerRow, 2, "行数");
        createHeaderCell(headerRow, 3, "数据大小");

        // 数据行
        for (MetadataTable table : tables) {
            Row dataRow = sheet.createRow(rowNum++);
            createDataCell(dataRow, 0, table.getTableName());
            createDataCell(dataRow, 1,
                    table.getTableComment() != null ? table.getTableComment() : "");
            createNumericDataCell(dataRow, 2,
                    table.getRowCount() != null ? table.getRowCount() : 0);
            createDataCell(dataRow, 3, formatBytes(0L /* getDataLength removed */));
        }

        // 自动调整列宽
        for (int i = 0; i < 4; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) + 1000);
        }
    }

    /**
     * 创建各表详情工作表
     */
    private void createTableDetailSheets(Workbook workbook, List<MetadataTable> tables) {
        for (MetadataTable table : tables) {
            createTableDetailSheet(workbook, table);
        }
    }

    /**
     * 创建单个表的详情工作表
     */
    private void createTableDetailSheet(Workbook workbook, MetadataTable table) {
        String sheetName = sanitizeSheetName(table.getTableName());
        Sheet sheet = workbook.createSheet(sheetName);
        int rowNum = 0;

        // 标题 - 表名
        Row titleRow = sheet.createRow(rowNum++);
        Cell titleCell = titleRow.createCell(0);
        titleCell.setCellValue("表: " + table.getTableName());
        titleCell.setCellStyle(titleStyle);
        sheet.addMergedRegion(new org.apache.poi.ss.util.CellRangeAddress(0, 0, 0, 7));

        rowNum++; // 空行

        // 表元数据
        createDataRow(sheet, rowNum++, "表注释",
                table.getTableComment() != null ? table.getTableComment() : "");
        createDataRow(sheet, rowNum++, "表类型",
                table.getTableType() != null ? table.getTableType().toString() : "");
        createDataRow(sheet, rowNum++, "行数",
                table.getRowCount() != null ? table.getRowCount().toString() : "0");

        rowNum++; // 空行

        // 字段信息表头
        Row headerRow = sheet.createRow(rowNum++);
        createHeaderCell(headerRow, 0, "序号");
        createHeaderCell(headerRow, 1, "列名");
        createHeaderCell(headerRow, 2, "数据类型");
        createHeaderCell(headerRow, 3, "长度");
        createHeaderCell(headerRow, 4, "可空");
        createHeaderCell(headerRow, 5, "主键");
        createHeaderCell(headerRow, 6, "默认值");
        createHeaderCell(headerRow, 7, "注释");

        // 查询字段数据
        List<MetadataColumn> columns = columnRepository.findByTableIdOrderByColumnPositionAsc(table.getId());

        if (columns.isEmpty()) {
            Row emptyRow = sheet.createRow(rowNum);
            Cell emptyCell = emptyRow.createCell(0);
            emptyCell.setCellValue("暂无字段信息");
            emptyCell.setCellStyle(dataStyle);
        } else {
            for (MetadataColumn column : columns) {
                Row dataRow = sheet.createRow(rowNum++);

                // 序号
                createNumericDataCell(dataRow, 0,
                        column.getColumnPosition() != null ? column.getColumnPosition() : rowNum - 5);

                // 列名
                createDataCell(dataRow, 1, column.getColumnName());

                // 数据类型
                createDataCell(dataRow, 2, column.getDataType());

                // 长度信息
                String lengthInfo = "";
                if (column.getColumnLength() != null && column.getColumnLength() > 0) {
                    lengthInfo = column.getColumnLength().toString();
                } else if (column.getColumnPrecision() != null && column.getColumnPrecision() > 0) {
                    lengthInfo = column.getColumnPrecision().toString();
                    if (column.getColumnScale() != null && column.getColumnScale() > 0) {
                        lengthInfo += "," + column.getColumnScale();
                    }
                }
                createDataCell(dataRow, 3, lengthInfo);

                // 可空
                createDataCell(dataRow, 4,
                        column.getNullable() != null && column.getNullable() ? "是" : "否");

                // 主键
                createDataCell(dataRow, 5,
                        column.getIsPrimaryKey() != null && column.getIsPrimaryKey() ? "是" : "否");

                // 默认值
                createDataCell(dataRow, 6,
                        column.getDefaultValue() != null ? column.getDefaultValue() : "");

                // 注释
                createDataCell(dataRow, 7,
                        column.getColumnComment() != null ? column.getColumnComment() : "");
            }
        }

        // 自动调整列宽
        for (int i = 0; i < 8; i++) {
            sheet.autoSizeColumn(i);
            // 限制最大宽度,避免过宽
            sheet.setColumnWidth(i, Math.min(sheet.getColumnWidth(i) + 1000, 15000));
        }
    }

    /**
     * 清理工作表名称中的非法字符
     * Excel工作表名称限制:
     * - 最多31个字符
     * - 不能包含: : \ / ? * [ ]
     */
    private String sanitizeSheetName(String tableName) {
        if (tableName == null) {
            return "未命名表";
        }

        // 移除非法字符
        String sanitized = tableName.replaceAll("[:\\\\/?*\\[\\]]", "_");

        // 限制长度
        if (sanitized.length() > 31) {
            sanitized = sanitized.substring(0, 31);
        }

        return sanitized;
    }

    /**
     * 创建表头单元格
     */
    private void createHeaderCell(Row row, int column, String value) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        cell.setCellStyle(headerStyle);
    }

    /**
     * 创建数据单元格(字符串)
     */
    private void createDataCell(Row row, int column, String value) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value != null ? value : "");
        cell.setCellStyle(dataStyle);
    }

    /**
     * 创建数据单元格(数值)
     */
    private void createNumericDataCell(Row row, int column, long value) {
        Cell cell = row.createCell(column);
        cell.setCellValue(value);
        cell.setCellStyle(dataStyle);
    }

    /**
     * 创建数据行(键值对)
     */
    private void createDataRow(Sheet sheet, int rowNum, String key, String value) {
        Row row = sheet.createRow(rowNum);
        createDataCell(row, 0, key);
        createDataCell(row, 1, value);
    }

    /**
     * 格式化字节数为可读格式
     */
    private String formatBytes(Long bytes) {
        if (bytes == null || bytes == 0) {
            return "0 B";
        }
        if (bytes < 1024) {
            return bytes + " B";
        }
        if (bytes < 1024 * 1024) {
            return String.format("%.2f KB", bytes / 1024.0);
        }
        if (bytes < 1024L * 1024 * 1024) {
            return String.format("%.2f MB", bytes / (1024.0 * 1024));
        }
        return String.format("%.2f GB", bytes / (1024.0 * 1024 * 1024));
    }

    /**
     * 清理文件名中的非法字符
     */
    private String sanitizeFileName(String fileName) {
        if (fileName == null) {
            return "export";
        }
        // 移除Windows文件名非法字符: < > : " / \ | ? *
        return fileName.replaceAll("[<>:\"/\\\\|?*]", "_");
    }
}
